﻿using System;
using System.Collections.Generic;
using System.Text;
using LibraryONTrack.dto;
using System.Data.SQLite;

namespace LibraryONTrack.dao
{
	public class BookDAO: BaseDAO, IBaseDAO<Book>
	{
		public List<Book> GetAll()
		{
			string query = @"SELECT
											   book_id, title
                       FROM book;";
			SqlDao dao = SqlDao;
			return dao.GetList<Book>(dao.GetSQLiteCommand(query));
		}

		public Book GetById(long id)
		{
			string query = @"SELECT
											   book_id, title
                       FROM book
											 WHERE book_id = @book_id;";

			SqlDao dao = SqlDao; 
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@book_id", id));
			return dao.GetSingle<Book>(command);
		}

		public Book GetByName(string title, bool useLike = false)
		{
			string query = @"SELECT
											   book_id, title
                       FROM book
											 WHERE title " + (useLike ? "LIKE" : "=") + " @title;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@title", title, 64));
			return dao.GetSingle<Book>(command);
		}


		public Book Insert(Book t)
		{
			SqlDao dao = SqlDao;
			string sql = @"insert into [book]
										   (title)
										 values
										   (@title);
										 select last_insert_rowid();";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@title", t.Title, 64));

			t.BookId = (long) dao.ExecuteScalar(command);
			return t;
		}

		public Book Update(Book t)
		{
			SqlDao dao = SqlDao;
			string sql = @"update [book]
										 set title = @title
										 where book_id = @book_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@title", t.Title, 64));
			command.Parameters.Add(dao.CreateParameter("@book_id", t.BookId));
			dao.ExecuteNonQuery(command);
			return t;
		}

		public void Delete(long id)
		{
			SqlDao dao = SqlDao;
			string sql = @"delete from [book]
										 where book_id = @book_id;";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@book_id", id));
			dao.ExecuteNonQuery(command);
		}

		public void Delete(Book t)
		{
			this.Delete(t.BookId);
		}

		public void Truncate()
		{
			SqlDao dao = new SqlDao();
			string sql = @"delete from book;
                     delete from sqlite_sequence where name='book';";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			dao.ExecuteNonQuery(command);
		}

	}
}

